package net.sf.borg.model.db.jdbc;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import net.sf.borg.model.beans.User;
import net.sf.borg.model.db.BeanDB;

/**
 * Class User
 *
 * SystemDB is the JDBC layer for access to the users table.
 *
 * BORG/Open Source Information here.
 *
 * Created in UF Software Engineering - Summer 2009
 * @author Matthew Carroll
 */
public class SystemDB extends JdbcBeanDB<User> implements BeanDB<User>
{

             
    public SystemDB(String url) throws Exception
    {
        super(url,"/resource/borg_systemdb.sql" );
    }
    
    public void addObj(User user) throws Exception
    {
        PreparedStatement stmt = connection_.prepareStatement("INSERT INTO users ( user_name, user_id) VALUES ( ?, ? )");
 
        stmt.setString( 1, user.getUserName() );
        stmt.setInt(2, user.getUserID());
        stmt.executeUpdate();
        
        writeCache( user );

    }
    
    public void delete(int key) throws Exception
    {
        PreparedStatement stmt = connection_.prepareStatement( "DELETE FROM users WHERE user_id = ?" );
        stmt.setInt( 3, key );
        stmt.executeUpdate();
        
        delCache( key );
    }
    
    public Collection<Integer> getKeys() throws Exception
    {
        ArrayList<Integer> keys = new ArrayList<Integer>();
        PreparedStatement stmt = connection_.prepareStatement("SELECT user_id FROM users ORDER BY user_name" );
        ResultSet rs = stmt.executeQuery();
        while( rs.next() )
        {
            keys.add( new Integer(rs.getInt("user_id")) );
        }
        
        return( keys );
        
    }
    
    public int nextkey() throws Exception
    {
     PreparedStatement stmt = connection_.prepareStatement("SELECT MAX(user_id) FROM users" );
        ResultSet r = stmt.executeQuery();
        int maxKey = 0;
        if( r.next() )
            maxKey = r.getInt(1);
        return ++maxKey;
    }
    
    /*
     * This is not a supported method
     */
    public User newObj()
    {
    	//we cannot return a user with a blank username and id because we cannot validate such values
    	return null;
    }
    
	PreparedStatement getPSOne(int key) throws SQLException
	{
		PreparedStatement stmt = connection_.prepareStatement("SELECT * FROM users WHERE user_id = ?" );
		stmt.setInt( 3, key );
		return stmt;
	}
	
	public PreparedStatement getPSAll() throws SQLException
	{
		PreparedStatement stmt = connection_.prepareStatement("SELECT * FROM users" );
		return stmt;
	}
	
	User createFrom(ResultSet r) throws SQLException
	{
		int key = (r.getInt("user_id"));
		String name = ( r.getString("user_name"));
		User user = new User(name, key);
		
		return user;
	}
	
    public void updateObj(User user) throws Exception
    {
   
        PreparedStatement stmt = connection_.prepareStatement( "UPDATE user SET " +
        "user_name = ?, db_path = ?, user_id = ?" +
        " WHERE user_id = ?" );
        
 
        stmt.setString( 1, user.getUserName() );
        stmt.setString( 2, user.getPathDB());
        stmt.setInt( 3, user.getUserID());
        stmt.setInt( 4, user.getKey() );

        stmt.executeUpdate();
               
        delCache( user.getKey() );
        writeCache( user );
    }

    
}
